<?php
error_reporting(E_ALL);

class MyDB extends SQLite3
{
    function __construct()
    {
        $this->open('buses');
    }
}

$db = new MyDB();
$db->exec('create table if not exists routes (point_id INTEGER primary key autoincrement, bus_id INTEGER, station_id INTEGER, reverse INTEGER, time TEXT, weekdays TEXT)');
$db->exec('create table if not exists stations (station_id integer primary key autoincrement, name TEXT, inner_name TEXT)');
$db->exec("create table if not exists buses (bus_id integer primary key autoincrement, name TEXT, number TEXT, start_station INTEGER, stop_station INTEGER)");
$i = 0; 
$stations = array();
$results = $db->query('SELECT * FROM stations order by station_id');
while ($res = $results->fetchArray(SQLITE3_ASSOC)) {
	if(!isset($res['station_id'])) continue; 

	$stations[$i]['station_id'] = $res['station_id']; 
	$stations[$i]['inner_name'] = $res['inner_name'];

	$i++; 
}
$outer = array();
foreach($stations as $station)
{
	$st = $station['inner_name'];
	$id = $station['station_id'];
	echo "Searching for station ".$st.'-'.$id."\n";
	
	if (!isset($outer[strtolower($st)]))
	{
		foreach($stations as $cst)
		{
		
			if ((strtolower($cst['inner_name']) == strtolower($st))&&($cst['station_id']!=$id))
			{
				$deletedid = $cst['station_id'];
				$db->exec("update routes set station_id = ".$id." where station_id = ".$deletedid);
				$db->exec("delete from stations where station_id = ".$deletedid);
				$outer[strtolower($cst['inner_name'])] = 1;
				echo "\tStation ".$st."\t\t-".$deletedid." killed\n";			
			}
			
		}
	}
	//unset($stations[$outer]);
	//$outer++;
}
$db->close();
echo "Script OK\n";
?>